# coding: utf-8
from xlwt import Workbook, easyxf
from xlrd import open_workbook

"""
ExcelClient module.

Created on 01/04/2018

@author: 强哥
"""

ROW_ONE_STYLE = 'pattern: pattern solid, pattern_fore_colour 3; font: name Times New Roman , bold on'


class ExcelClient(object):
	
	def __init__(self):
		self.__writeWorkBookInstance = None
		self.__sheet = None
		self.rowOneStyle = None

		self.__readWorkBookInstance = None
		self.__readSheetNames = []

	def excelToItem(self, fileName):
		"""
		
		execl 转 数组
		"""
		self.__setReadWorkBookInstance(fileName)
		self.__getReadSheetNames()

		readSheetDatas = []
		for sheetName in self.__readSheetNames:
			sheetData = self.__getReadSheetData(sheetName)
			if sheetData:
				readSheetDatas.append(sheetData)
		return readSheetDatas

	def __setReadWorkBookInstance(self, fileName):
		"""

		创建 execl 读工作簿实例
		"""
		self.__readWorkBookInstance = open_workbook(fileName)

	def __getReadSheetNames(self):
		"""

		获取 execl 所有表格名
		"""
		self.__readSheetNames = self.__readWorkBookInstance.sheet_names()

	def __getReadSheetData(self, sheetName):
		"""

		获取表格数据
		"""
		sheet = self.__readWorkBookInstance.sheet_by_name(sheetName)
		return [sheet.row_values(i) for i in range(sheet.nrows)]

	def itemToExcel(self, rowOne, rows, fileName):
		"""

		数组保存成Execl
		"""
		self.__setWriteWorkBookInstance()
		self.__addSheet()

		self.__writeRow(rowOne, 0)

		for rowNum, rowVal in enumerate(rows):
			self.__writeRow(rowVal, rowNum+1)

		self.__saveExcel(fileName)

	def __saveExcel(self, fileName):
		"""

		保存文件
		"""
		self.__writeWorkBookInstance.save(fileName)

	def __writeRow(self, row, rowNum):
		"""
		
		写入行
		"""
		for col, colVal in enumerate(row):
			if rowNum == 0:
				rowOneStyle = self.rowOneStyle or easyxf(ROW_ONE_STYLE)
				self.__sheet.write(rowNum, col, colVal, rowOneStyle)
			else:
				self.__sheet.write(rowNum, col, colVal)

	def __setWriteWorkBookInstance(self):
		"""
		
		创建execl 写工作簿实例
		"""
		self.__writeWorkBookInstance = Workbook(encoding='utf-8')

	def __addSheet(self):
		"""
		
		添加 execl 表格, 并命名
		"""
		self.__sheet = self.__writeWorkBookInstance.add_sheet('sheet01')

def example():
	"""

	例子
	"""
	client = ExcelClient()
	rowOne = ['a', 'b', 'c']
	rows = [[1, 2, 3],[11, 21, 31],[12, 22, 32], ['哈哈', u'很好', u'笑一笑']]
	fileName = 'test.xls'

	client.itemToExcel(rowOne, rows, fileName)
	item = client.excelToItem(fileName)
	print item

if __name__ == '__main__':
	example()